package com.bilibili.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.bilibili.dao.BaseDao;
import com.bilibili.dao.VideoAndCommentDao;
import com.bilibili.dto.VideoAndComment;

public class VideoAndCommentDaoImpl implements VideoAndCommentDao {
	
	@Override
	public List<VideoAndComment> selectAllVideo(int userId,int pageNum,int pageSize,String title) {
		// TODO Auto-generated method stub
		Connection conn=BaseDao.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<VideoAndComment> videoList = new ArrayList<VideoAndComment>();
		String sql = "SELECT * FROM video WHERE uploader_id = ? and status = 1 ";
		if(title != null && !"".equals(title)) {
			sql+="AND title like concat('%',?,'%') ";
		}
		sql+="ORDER BY create_time DESC LIMIT ?,?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, userId);
			if(title != null && !"".equals(title)) {
				ps.setString(2, title);
				ps.setInt(3, (pageNum-1)*pageSize);
				ps.setInt(4, pageSize);
			}else {
				ps.setInt(2, (pageNum-1)*pageSize);
				ps.setInt(3, pageSize);
			}
			
			rs =  ps.executeQuery();
			while(rs.next()) {
				VideoAndComment video = new VideoAndComment();
				video.setVideoId(rs.getInt("video_id"));
			    video.setTitle(rs.getString("title"));
			    video.setDescription(rs.getString("description"));
			    video.setCoverUrl(rs.getString("cover_url"));
			    video.setVideoUrl(rs.getString("video_url"));
			    video.setCategoryId(rs.getInt("category_id"));
			    video.setUploader_id(rs.getInt("uploader_id"));
			    video.setCreateTime(rs.getDate("create_time"));
			    video.setDuration(rs.getInt("duration"));
			    video.setState(rs.getInt("status"));
			    video.setViewCount(rs.getInt("view_count"));
			    video.setLikeCount(rs.getInt("like_count"));
			    video.setCoinCount(rs.getInt("coin_count"));
			    video.setCollectCount(rs.getInt("collect_count"));
				videoList.add(video);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {			
			BaseDao.closeAll(conn, ps,rs);
		}
		return videoList;
	}

	@Override
	public int selectAllVideoNum(int userId, String title) {
		// TODO Auto-generated method stub
		Connection conn = BaseDao.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		int row = 0;
		String sql = "SELECT count(1) as total FROM video WHERE uploader_id = ? and status = 1 ";
		if(title != null && !"".equals(title)) {
			sql+="AND title like concat('%',?,'%') ";
		}
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, userId);
			if(title != null && !"".equals(title)) {
				ps.setString(2, title);
			}
			rs =  ps.executeQuery();
			if(rs.next()) {
				row = rs.getInt("total");
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {			
			BaseDao.closeAll(conn, ps,rs);
		}
		return row;
	}
}
